package dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import boerrors.ClientParticulierExistantException;
import boerrors.ClientParticulierInexistantException;
import dao.ClientParticulierDao;
import dao.dto.ClientParticulierDto;
import dao.dto.AdresseDto;
import dao.dto.CodePostalDto;
import dao.dto.ZoneDto;

public class ClientParticulierDaoImpl extends ClientParticulierDao {
    private static ClientParticulierDao _instance = null;
    private CallableStatement _callableStatement = null;
    private SQLConnection _connection = new SQLConnection();

    //Constructeur du singleton
    public static ClientParticulierDao getInstance(){
            if(_instance == null)
                    _instance = new ClientParticulierDaoImpl();
            return _instance;
    }

    //Methodes   
    @Override
    public int enregistrer(ClientParticulierDto clientParticulier) throws ClientParticulierExistantException {
            try {
                    System.out.println("Entrer Dans la DAL pour création d'un clientP");
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{? = call sp_ClientParticulierInsert(?,?,?,?,?,?,?,?)}");
                    // valeur de retour (id)                   
                    _callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
                    //parameter
                    _callableStatement.setString("nom",clientParticulier.getNom());
                    _callableStatement.setString("prenom",clientParticulier.getPrenom());
                    _callableStatement.setString("telephone",clientParticulier.getTelephone());                       
                    _callableStatement.setString("email",clientParticulier.getEmail());
                    _callableStatement.setString("numeroTVA",clientParticulier.getNumeroTVA());                        
                    _callableStatement.setString("rue",clientParticulier.getAdresse().getRue());
                    _callableStatement.setString("numero",clientParticulier.getAdresse().getNumero());
                    _callableStatement.setInt("CodePostalFK",clientParticulier.getAdresse().getCodePostal().getIdCodePostal());
                    //execute
                    _callableStatement.executeUpdate();    
      
                    return _callableStatement.getInt(1);
            } catch(SQLException e) {
                if (e.getMessage().equals("547")) {
                    throw new ClientParticulierExistantException("Code postal/Adresse Incorrect (FK)",e);
                } else if (e.getMessage().equals("515")) {
                    throw new ClientParticulierExistantException("Non autorisé champs vide",e);
                } else  throw new ClientParticulierExistantException(e);

            } catch(Exception e){
                throw new ClientParticulierExistantException(e);
            } finally {
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientParticulierExistantException(e);}
                    _connection.deconnecter();
            }
    }  

    @Override
    public void modifier(ClientParticulierDto clientParticulier) throws ClientParticulierInexistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientParticulierUpdate(?,?,?,?,?,?,?,?,?)}");

                    //parameter                                               
                    _callableStatement.setInt("idClient",clientParticulier.getIdClient());
                    _callableStatement.setString("nom",clientParticulier.getNom());
                    _callableStatement.setString("prenom",clientParticulier.getPrenom());
                    _callableStatement.setString("telephone",clientParticulier.getTelephone());
                    _callableStatement.setString("email",clientParticulier.getEmail());
                    _callableStatement.setString("numeroTVA",clientParticulier.getNumeroTVA());
                    //_callableStatement.setInt("idAdresse",clientParticulier.getAdresse().getIdAdresse());
                    _callableStatement.setString("rue",clientParticulier.getAdresse().getRue());
                    _callableStatement.setString("numero",clientParticulier.getAdresse().getNumero());
                    _callableStatement.setInt("codePostalFK",clientParticulier.getAdresse().getCodePostal().getIdCodePostal());

                    //execute
                    _callableStatement.executeUpdate();    
            } catch(SQLException e) {
                if (e.getMessage().equals("547")) {
                    throw new ClientParticulierInexistantException("Code postal/Adresse/ Incorrect (FK)",e);
                } else if (e.getMessage().equals("515")) {
                    throw new ClientParticulierInexistantException("Non autorisé champs vide",e);
                } else if (e.getMessage().equals("50001")) {
                    throw new ClientParticulierInexistantException("Le client n'existe pas",e);
                } else if (e.getMessage().equals("50002")) {
                    throw new ClientParticulierInexistantException("L'adresse n'a pas été trouvé",e);
                } else  throw new ClientParticulierInexistantException(e);                  
            } catch(Exception e){
                throw new ClientParticulierInexistantException(e);
            } finally {
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientParticulierInexistantException(e);}
                    _connection.deconnecter();
            }
  }


    @Override
    public void supprimer(ClientParticulierDto clientParticulier) throws ClientParticulierInexistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientParticulierDelete(?)}");

                    //parameter                                               
                    _callableStatement.setInt("idClient",clientParticulier.getIdClient());                      

                    //execute
                    _callableStatement.executeUpdate();    
            } catch(SQLException e) {
                if (e.getMessage().equals("50001")) {
                    throw new ClientParticulierInexistantException("Le client n'existe pas",e);
                } else if (e.getMessage().equals("50002")) {
                    throw new ClientParticulierInexistantException("Suppression non autorisée: client utilisé dans les interventions",e);
                } else  throw new ClientParticulierInexistantException(e);                  
            } catch(Exception e){
                throw new ClientParticulierInexistantException(e);
            } finally {
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientParticulierInexistantException(e);}
                    _connection.deconnecter();
            }
    }

    @Override
    public Vector<ClientParticulierDto> lister() throws ClientParticulierInexistantException {
        ResultSet resultat = null;
        ZoneDto zone = null;
        CodePostalDto codePostal = null;
        AdresseDto adresse = null;
        ClientParticulierDto client = null;
            try {                                      
                //query
                _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientParticulierList}");    
                //execute
                resultat = _callableStatement.executeQuery();
                
                Vector<ClientParticulierDto> liste = new Vector<ClientParticulierDto>();
                while (resultat.next()) {
                    zone = new ZoneDto( resultat.getInt("idZone"),
                                        resultat.getString("libZone"));
                    codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                    resultat.getString("cp"),
                                                    resultat.getString("ville"),
                                                    resultat.getString("libPays"),
                                                    zone);
                    adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                resultat.getString("rue"),
                                                resultat.getString("numero"),
                                                codePostal
                                                );
                    client = new ClientParticulierDto(  resultat.getInt("idClient"),
                                                        resultat.getString("nom"),
                                                        resultat.getString("prenom"),
                                                        adresse,
                                                        resultat.getString("telephone"),
                                                        resultat.getString("email")
                                                        );
                    if (resultat.getString("numeroTVA")!=null) {
                        client.setNumeroTVA(resultat.getString("numeroTVA"));
                    }
                    
                    
                    liste.add(client);
                }
                return liste;
            } catch(SQLException e) { 
                throw new ClientParticulierInexistantException(e);
            } finally { 
                if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new ClientParticulierInexistantException(e);}
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new ClientParticulierInexistantException(e);}
                    _connection.deconnecter();
            }
    }
    
    /*@Override
    public ClientParticulierDto rechercher(long numeroClient) throws ClientInexistantException {
            ResultSet resultat = null;
            ZoneDto zone = null;
            AdresseDto adresse = null;
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ParticulierSelectNumeroClient(?)}");
                    //parameter
                    _callableStatement.setLong("numeroClient",numeroClient);
                    //execute
                    resultat = _callableStatement.executeQuery();    
                    if(!resultat.next()) throw new ClientInexistantException();
                    zone = new ZoneDto(resultat.getString("ZONE"));
                    adresse = new AdresseDto(   resultat.getString("RUE"),
                                                resultat.getString("NUMERO"),
                                                resultat.getString("CODEPOSTAL"),
                                                resultat.getString("VILLE"),
                                                resultat.getString("PAYS"),
                                                zone);
                    return new ClientParticulierDto(    resultat.getLong("NUMEROCLIENT"),
                                                        resultat.getString("NOM"),
                                                        resultat.getString("PRENOM"),
                                                        adresse,
                                                        resultat.getString("TELEPHONE"),
                                                        resultat.getString("EMAIL"),
                                                        resultat.getString("NUMEROTVA"));
            }catch(SQLException e){throw new ClientInexistantException(e);
            }finally{           
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                    _connection.deconnecter();}
    }

    public ClientParticulierDto rechercher(String numeroTVA) throws ClientInexistantException {
            ResultSet resultat = null;
            ZoneDto zone = null;
            AdresseDto adresse = null;
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ParticulierSelectNumeroTVA(?)}");
                    //parameter
                    _callableStatement.setString("numeroTVA",numeroTVA);
                    //execute
                    resultat = _callableStatement.executeQuery();    
                    if(!resultat.next()) throw new ClientInexistantException();
                    zone = new ZoneDto(resultat.getString("ZONE"));
                    adresse = new AdresseDto(   resultat.getString("RUE"),
                                                resultat.getString("NUMERO"),
                                                resultat.getString("CODEPOSTAL"),
                                                resultat.getString("VILLE"),
                                                resultat.getString("PAYS"),
                                                zone);
                    return new ClientParticulierDto(    resultat.getLong("NUMEROCLIENT"),
                                                        resultat.getString("NOM"),
                                                        resultat.getString("PRENOM"),
                                                        adresse,
                                                        resultat.getString("TELEPHONE"),
                                                        resultat.getString("EMAIL"),
                                                        resultat.getString("NUMEROTVA"));
            }catch(SQLException e){throw new ClientInexistantException(e);
            }finally{
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                    _connection.deconnecter();}
    }    

    @Override
    public Vector<ClientParticulierDto> rechercher(String nom, String prenom) throws ClientInexistantException {
            ResultSet resultat = null;
            ZoneDto zone = null;
            AdresseDto adresse = null;
            ClientParticulierDto client = null;
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ParticulierSelectNomPrenom(?,?)}");
                    //parameter
                    _callableStatement.setString("nom",nom);
                    _callableStatement.setString("prenom",prenom);
                    //execute
                    resultat = _callableStatement.executeQuery();    
                    if(!resultat.next()) throw new ClientInexistantException();
                    Vector<ClientParticulierDto> liste = new Vector<ClientParticulierDto>();
                    while(resultat.next()){
                            zone = new ZoneDto(resultat.getString("ZONE"));
                            adresse = new AdresseDto(   resultat.getString("RUE"),
                                                        resultat.getString("NUMERO"),
                                                        resultat.getString("CODEPOSTAL"),
                                                        resultat.getString("VILLE"),
                                                        resultat.getString("PAYS"),
                                                        zone);
                            client = new ClientParticulierDto(  resultat.getLong("NUMEROCLIENT"),
                                                                resultat.getString("NOM"),
                                                                resultat.getString("PRENOM"),
                                                                adresse,
                                                                resultat.getString("TELEPHONE"),
                                                                resultat.getString("EMAIL"),
                                                                resultat.getString("NUMEROTVA"));
                            liste.add(client);}
                    return liste;
            }catch(SQLException e){throw new ClientInexistantException(e);
            }finally{_connection.deconnecter();}
    }

    @Override
    public Vector<ClientParticulierDto> rechercher(AdresseDto adresse) throws ClientInexistantException {
            ResultSet resultat = null;
            ZoneDto zone = null;
            ClientParticulierDto client = null;
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_ParticulierSelectAdresse(?,?)}");
                    //parameter
                    _callableStatement.setString("rue",adresse.getRue());
                    _callableStatement.setString("numero",adresse.getNumero());
                    _callableStatement.setString("codePostal",adresse.getCodePostal());
                    //execute
                    resultat = _callableStatement.executeQuery();    
                    if(!resultat.next()) throw new ClientInexistantException();
                    Vector<ClientParticulierDto> liste = new Vector<ClientParticulierDto>();
                    while(resultat.next()){
                            zone = new ZoneDto(resultat.getString("ZONE"));
                            adresse = new AdresseDto(   resultat.getString("RUE"),
                                                        resultat.getString("NUMERO"),
                                                        resultat.getString("CODEPOSTAL"),
                                                        resultat.getString("VILLE"),
                                                        resultat.getString("PAYS"),
                                                        zone);
                            client = new ClientParticulierDto(  resultat.getLong("NUMEROCLIENT"),
                                                                resultat.getString("NOM"),
                                                                resultat.getString("PRENOM"),
                                                                adresse,
                                                                resultat.getString("TELEPHONE"),
                                                                resultat.getString("EMAIL"),
                                                                resultat.getString("NUMEROTVA"));
                            liste.add(client);}
                    return liste;
            }catch(SQLException e){throw new ClientInexistantException(e);
            }finally{_connection.deconnecter();}
    }*/      
}